Hello everyone.
In this lecture we will be covering data and time related functions in excel.
We all mention date and time in different formats.
Sometimes we use date Montagnier which is the reason I'm on my way or sometimes it is Monday year MDD
by way of campaigns the month is written in words instead of no Excel supports all this format and it
even identified the important cells.
If those are data or not for example if I type 1 arg and press into it picks up that it is dead automatically
and in a formula but it is written in the default date format one function which we will use to see
today's date in default.
What matters is equal to today.
Function this just returns to today's date whenever the food is refreshed.
This formula is also refreshed.
So this is showing girls no right now.
But if a closed issued an opening tomorrow it will show in November.
Similar is the now function we take is equal to now.
And you can see this includes the time also along with the date important thing to know here is although
Excel is showing us date aim and readable format in the back it maintains dates and times in number
format.
If it takes the data number format we can see that it is converted to a real number.
The immediate part of this number is actually the count of days from first and nineteen hundred so or
plus Jan 19 under the value will be 1 for December nineteen hundred.
This value will be 365 Today is world number two thousand eighteen which is forty three thousand four
hundred fifteen days from Jan nineteen hundred.
The decimal part the naughty time so 24 hours will be one unit for twelve hours it will be equal we're
going to fall into one is equal 2.5 for six hours it will be six by twenty four we just point to five
right now it is four that it will be him which is sixteen our study dominant which is coming out to
be point six nine.
Now we will see some common daytime format available in Excel for this date
if we want it in some other format right click on the cell intellect for mental in the category menu
we can select date to look at different data formats we can select time in the category menu to look
at different time format we can also create a custom format go to type click on custom go to type feel
and maintain the format or bit we relate Billy FORMAN If you want complete mind ape M M M M
for year lively and then time is its column and then
we can see that the daytime value is now shown in the right format.
Next we look at day month and year formulas for any particular date we can separate different parts
of it using these formulas so to get which day of the month it is a day and selected it
today's world they are no.
So twelve is deep solution
we had the count of month in any year use is equal to month and selected November 18th Amendment.
So we are getting live in is the answer.
Similarly to good air time is equal to air until needed getting two thousand eighteen in this
important operations that we usually need to do on base and values involve finding the number of days
between two dates or finding time difference between two timestamps to find the number of days between
two dates.
You can use is equal to days and selected to this date.
This returns the number of days between the two dates listed
we can see that between 12:00 Nambour and 1st November.
There is a difference of eleven dates.
Similarly for the next case there is a difference of four hundred forty days.
You can also do this by simply separating the two date.
You can see it is giving us the same result.
You can figure out why this work by recalling that date in excel are Captain number format and difference
of those numbers and integers is the number of days they are apart.
To get this difference in us multiply this value with twenty four
to get it in a minute but then multiply it with 60
third way to find difference in the due date.
If the data function this function allows you to specify what you want in return whether you want different
in days months or years.
For days we tape is equal to dative if
start date.
Comma end date on a B for days and for months and y for years.
So you can see that in the second row.
There is a difference of 15 months.
Between these two dates selected similarly it can be done for getting the difference in is
that already selected in the neglected.
We will discuss the index match and look functions.
Thanks.